﻿using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Text;
using CsDbHelperFactory;
namespace CsDbHelper
{
    public class SqlDbHelper : ISqlDbHelper
    {

        private System.Data.SqlClient.SqlConnection _Conn = new System.Data.SqlClient.SqlConnection();
        private string _ConnString = "";
        private string _SqlString = "";
        private System.Data.SqlClient.SqlCommand _command;
        private StringBuilder _sqlBuilder;
        private int _count = 0;
        public string ConnectionString
        {
            get { return _ConnString; }
            set { _ConnString = value; }
        }

        public SqlDbHelper()
        {
            string connString = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString");
            _ConnString = connString;//将读取到的属性放入私有变量
            if (connString == null | string.IsNullOrEmpty(connString) | string.IsNullOrEmpty(connString))
            {
                throw new Exception("请配置初始化的连接字符串，否则请提供连接字符串");
            }
            Create(connString);
        }
        public SqlDbHelper(string connString)
        {
            Create(connString);
        }
        private void Create(string connString)
        {
            //改动：2014年3月20日22:05:34:
            //功能：将打开连接的功能关闭, 仅在执行的时候打开, 执行后关闭
            _ConnString = connString;
            _sqlBuilder = new StringBuilder("");
            _Conn = new SqlConnection(_ConnString);
            _command = new SqlCommand();
            _command.Connection = _Conn;

        }
        public SqlDbHelper Create()
        {
            return new SqlDbHelper();
        }

        public int ExcuteNonQuery()
        {
            return ExcuteNonQuery(this._command);
        }

        public int ExcuteNonQuery(System.Data.SqlClient.SqlCommand sqlComm)
        {
            _Conn.Open();
            try
            {
                using (sqlComm)
                {
                    sqlComm.CommandText = _sqlBuilder.ToString();
                    return sqlComm.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                _Conn.Close();
                throw ex;
            }

            //
        }

        public T ExecuteScalar<T>()
        {
            return ExecuteScalar<T>(this._command);
        }
        public T ExecuteScalar<T>(System.Data.SqlClient.SqlCommand sqlComm)
        {
            //更新：改为在执行中打开连接，执行后关闭
            object obj = null;
            using (sqlComm)
            {
                _Conn.Open();
                sqlComm.CommandText = _sqlBuilder.ToString();
                obj = sqlComm.ExecuteScalar();
                _Conn.Close();
                if (((obj == null) || DBNull.Value.Equals(obj)))
                {
                    return default(T);
                }
                if ((object.ReferenceEquals(obj.GetType(), typeof(T))))
                {
                    return (T)obj;
                }
                return (T)Convert.ChangeType(obj, typeof(T));
            }
        }

        public System.Data.DataSet FillDataSet()
        {
            return FillDataSet(this._command);
        }

        public System.Data.DataTable FillDataTable()
        {
            return FillDataTable(this._command);
        }

        public SqlDbHelper From(string sqlStr)
        {
            return new SqlDbHelper(sqlStr);
        }

        public System.Data.DataTable FillDataTable(System.Data.SqlClient.SqlCommand sqlComm)
        {
            //更新：改为在执行中打开连接，执行后关闭
            DataTable _DataTable = new DataTable("_sunsoft");
            using (sqlComm)
            {
                sqlComm.CommandText = _sqlBuilder.ToString();
                _Conn.Open();
                using (System.Data.SqlClient.SqlDataReader dbReader = sqlComm.ExecuteReader())
                {
                    _DataTable.Load(dbReader);
                }
                _Conn.Close();
            }
            return _DataTable;
        }

        public System.Data.DataSet FillDataSet(System.Data.SqlClient.SqlCommand sqlComm)
        {
            //更新：改为在执行中打开连接，执行后关闭
            DataSet _DataSet = new DataSet();
            using (sqlComm)
            {
                sqlComm.CommandText = _sqlBuilder.ToString();
                _Conn.Open();
                dynamic s = new SqlDataAdapter(sqlComm).Fill(_DataSet);
                _Conn.Close();
                int sCount = 0;
                for (sCount = 0; sCount <= _DataSet.Tables.Count - 1; sCount++)
                {
                    _DataSet.Tables[sCount].TableName = ("_sunsoft" + sCount.ToString());
                }
                return _DataSet;
            }
        }


        ///后面的代码用于运算符重载相关的代码
        public static SqlDbHelper operator +(SqlDbHelper meDefault, string strp)
        {
            //完成:运算符重载
            meDefault._addSqlText(strp);
            return meDefault;
        }

        private void _addSqlText(string paramStr)
        {
            _sqlBuilder.Append(paramStr);
        }
        public static SqlDbHelper operator +(SqlDbHelper meDefault, QueryParameter strp)
        {
            //meDefault._sqlBuilder.Append("@" & _count.ToString)
            meDefault._addParam(strp);
            return meDefault;
        }
        /// <summary>
        /// 添加参数，重载，参数类型
        /// </summary>
        /// <param name="p">直接数据类型</param>
        /// <remarks></remarks>
        private void _addParam(QueryParameter p)
        {
            this._sqlBuilder.Append("@p" + _count.ToString());
            this._command.Parameters.AddWithValue("@p" + _count.ToString(), p.Value);
        }
    }
}